package com.sec.etech.org.repository;

import com.alibaba.fastjson.JSONArray;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class OrgUserSyncHrsRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public boolean del(JSONArray jsonArray) {
        String id = "(";
        for (int i = 0; i < jsonArray.size(); i++) {
            id += "'" + jsonArray.getJSONObject(i).getString("uid") + "'";
            if (i < jsonArray.size() - 1)
                id += ",";
        }
        id += ")";

        String sql = "UPDATE org_user_sync_hrs SET update_remark=null where id_ in " + id;

        try {
            jdbcTemplate.execute(sql);
            return true;
        } catch (EmptyResultDataAccessException e) {
            return false;
        }
    }

    public boolean sync() {
        return true;
    }

    //同步表中的create_by_、update_by_是工号，需转换成ETech的用户id，group_id是org_group里的code，也需要进行转换
    public boolean diff() {
        String sql =
                "UPDATE org_user_sync_hrs SET update_remark=''; " +
                        "UPDATE org_user_sync_hrs a INNER JOIN org_user b ON a.account_=b.account_  " +
                        "SET a.update_remark=CONCAT(a.update_remark,'fullname_,') " +
                        "WHERE a.fullname_<>b.fullname_; " +
                        "UPDATE org_user_sync_hrs a INNER JOIN org_user b ON a.account_=b.account_  " +
                        "SET a.update_remark=CONCAT(a.update_remark,'email_,') " +
                        "WHERE a.email_<>b.email_ AND a.email_ IS NOT NULL AND a.email_<>''; " +
                        "UPDATE org_user_sync_hrs a INNER JOIN org_user b ON a.account_=b.account_  " +
                        "SET a.update_remark=CONCAT(a.update_remark,'sex_,') " +
                        "WHERE a.sex_<>b.sex_; " +
                        "UPDATE org_user_sync_hrs a INNER JOIN org_user b ON a.account_=b.account_  " +
                        "SET a.update_remark=CONCAT(a.update_remark,'status_,') " +
                        "WHERE a.status_<>b.status_; " +

                        "UPDATE org_user_sync_hrs a INNER JOIN org_user b ON a.account_=b.account_  " +
                        "SET a.update_remark=CONCAT(a.update_remark,'remark_,') " +
                        "WHERE a.remark_<>b.remark_ AND a.remark_ IS NOT NULL AND a.remark_<>''; " +

                        "UPDATE org_user_sync_hrs a INNER JOIN org_user_group_view b ON a.account_=b.account_ LEFT JOIN org_group c ON a.group_id = c.code_  " +
                        "SET a.update_remark=CONCAT(a.update_remark,'group_id,') " +
                        "WHERE b.group_id_<>c.id_;" +
                        "UPDATE org_user_sync_hrs SET update_remark=CONCAT(update_remark,'account_,') " +
                        "WHERE account_ NOT IN (SELECT account_ FROM org_user); ";
        try {
            jdbcTemplate.execute(sql);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
}
